from flask import Blueprint,request,jsonify,make_response
from flask import current_app as app
import os
from db import db1
from utils.myfile import myf

bp_company=Blueprint('company',__name__)

@bp_company.route('/test')
async def test():
    str = '请求IP'+request.remote_addr+',请求的url'+request.url+'错误信息'+'\n'
    app.logger.error(str)
    return 'ok'

def xtree(data):
    if len(data) <= 0:
        return data
    list =[]
    dict ={}
    for i in data:
        i['label'] = i['name']
        dict[i['id']]=i

    for j in data:
        pid = j['pid']
        j['label']=j['name']
        if pid==0:
            list.append(j)
        else:

            if 'children' not in dict[pid]:
                dict[pid]['children']=[]
            dict[pid]['children'].append(j)
    return list


# 导入
@bp_company.route('/upload',methods=['POST'])
def upload():
    # 获取文件
    file = request.files['file']
    print(file)
    newname = myf.rename(file.filename)
    # 上传
    file.save(os.path.join('./static/files/'+newname))
    # 打开文件读取
    name = './static/files/'+newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(2, nrows):
        row_date = sh.row_values(i)
        # print(row_date[0])
        # print(row_date[1])
        for j in range(0,ncols):
            print(row_date[j])
    return 'ok'

# 获取组织
@bp_company.route('/organization')
def organization():
    sql = "select * from organization"
    res = db1.findall(sql)
    list = xtree(res)
    # print(res)
    return  jsonify({'code':200,'list':list})

from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
#导出
@bp_company.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    # 写sql语句
    # sql = "select * from organization limit 0,10"
    # res = db1.find_all(sql)
    table.write(0, 0, "编号")
    table.write(0, 1, "部门")
    # 如果sql语句存在 则循环
    # num=1
    # for i in res:
    #     table.write(num,0,i['id'])
    #     table.write(num,1, i['name'])
    #     num+=1
    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


@bp_company.route('/getuser')

def getuser():
    sql = "select * from user"
    res = db1.findall(sql)
    return jsonify({'code':200,'list':res})

# 搜索
from utils.es import Es
@bp_company.route('/getdata')
def getdata():
    es = Es('testes','test-type')
    body = {
        "multi_match": {
            "query": "abc",
            "fields": ["name", "content"]
        }
    }
    data = es.search(body)
    data = data['hits']['hits']
    print(data)
    return 'ok'